package com.example.dps;

import java.util.ArrayList;

import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

public class AccountDBHelper extends SQLiteOpenHelper{
	
	SQLiteDatabase db;
	
	String tag = "AccountDBHelper";
	
	public AccountDBHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
		// TODO Auto-generated constructor stub
	}
	
	public AccountDBHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		// TODO Auto-generated constructor stub
		
		this.db = getWritableDatabase();
		
		
		//db.execSQL("DROP TABLE IF EXISTS " + ACCOUNT_TABLE_NAME);
		
		
		/** create accounts table **/
		db.execSQL(CREATE_ACCOUNTS_TABLE);

		
		this.db.close();

	}
	
	
	
	/**********************************************************************
	 * Database Variables
	 */
    public static final int DATABASE_VERSION = 1;
    public static final String DATABASE_NAME = "DownPaymentSaver.db";
    /********************************************************************/


	/**********************************************************************
     * Accounts Table Variables
     */
    public static final String ACCOUNT_TABLE_NAME = "Accounts";
    public static final String COLUMN_ACCOUNT_ID = "_id";
    public static final String COLUMN_ACCOUNT_NAME = "name";
    public static final String COLUMN_ACCOUNT_CHECKNUM = "checking";
    public static final String COLUMN_ACCOUNT_ROUTENUM = "routing";
    public static final String COLUMN_ACCOUNT_AMOUNT = "amount";
    public static final String COLUMN_ACCOUNT_FULLNAME = "fullName";
    public static final String COLUMN_ACCOUNT_BIRTHDATE = "birthdate";
    public static final String COLUMN_ACCOUNT_SOCIAL_SECURITY = "socialSecurity";
    public static final String COLUMN_ACCOUNT_EMPLOYER = "employer";
    public static final String COLUMN_ACCOUNT_PHONE_NUMBER = "phoneNumber";
    public static final String COLUMN_ACCOUNT_HOME_ADDRESS = "homeAddress";
    public static final String COLUMN_ACCOUNT_HOME_CITY = "homeCity";
    public static final String COLUMN_ACCOUNT_HOME_STATE = "homeState";
    public static final String COLUMN_ACCOUNT_HOME_ZIP = "homeZip";
    
    public static final String CREATE_ACCOUNTS_TABLE = "CREATE TABLE IF NOT EXISTS " + ACCOUNT_TABLE_NAME +
    		" (" + 
    		COLUMN_ACCOUNT_ID + " INTEGER, "+ 
    		COLUMN_ACCOUNT_NAME+" VARCHAR(180), " +
    		COLUMN_ACCOUNT_CHECKNUM + " VARCHAR(180)," + 
    		COLUMN_ACCOUNT_ROUTENUM + " VARCHAR(180), " +
    		COLUMN_ACCOUNT_AMOUNT + " double," +
    		COLUMN_ACCOUNT_FULLNAME + " VARCHAR(80),"+
    		COLUMN_ACCOUNT_BIRTHDATE + " VARCHAR(12),"+
    		COLUMN_ACCOUNT_SOCIAL_SECURITY + " VARCHAR(12),"+
    		COLUMN_ACCOUNT_EMPLOYER + " VARCHAR(80)," + 
    		COLUMN_ACCOUNT_PHONE_NUMBER + " VARCHAR(10),"+
    		COLUMN_ACCOUNT_HOME_ADDRESS + " VARCHAR(120),"+
    		COLUMN_ACCOUNT_HOME_CITY + " VARCHAR(50),"+
    		COLUMN_ACCOUNT_HOME_STATE + " VARCHAR(30),"+
    		COLUMN_ACCOUNT_HOME_ZIP + " INTEGER"+
    		");";
    /** End Account Table Variables *************************************/
    
    
    
    
    /********************************************************************
     * Down Payment Saver Account Entry Variables
     */
    
    public static final String DPS_ACCOUNT_NAME = "Down Payment Saver";
    
    /** End DPS Account Entry Variables ********************************/
    

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		
		db.execSQL("DROP TABLE IF EXISTS " + ACCOUNT_TABLE_NAME);
		
		/** create accounts table **/
		db.execSQL(CREATE_ACCOUNTS_TABLE);
		
		/** populate database with 2 accounts. DPS account and Checking Account **/
		//addAccountInit(db, "MTB Checking", "123456789", "1234567", 1500);
		//addAccountInit(db, DPS_ACCOUNT_NAME, "987654321", "7654321", 0);
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
	}
    
	
	public void addAccount(Account account){
		
		this.db = getWritableDatabase();
		
		String insertStmt = "INSERT INTO " + ACCOUNT_TABLE_NAME + " ("+
				COLUMN_ACCOUNT_NAME + ", " +
				COLUMN_ACCOUNT_ID + ", " +
				COLUMN_ACCOUNT_CHECKNUM + ", " +
				COLUMN_ACCOUNT_ROUTENUM + ", " +
				COLUMN_ACCOUNT_AMOUNT + ", " +
			
				COLUMN_ACCOUNT_FULLNAME + ", " +
				COLUMN_ACCOUNT_BIRTHDATE + ", " +
				COLUMN_ACCOUNT_SOCIAL_SECURITY + ", " +
				COLUMN_ACCOUNT_EMPLOYER + ", " +
				COLUMN_ACCOUNT_PHONE_NUMBER + ", " +
			 
				COLUMN_ACCOUNT_HOME_ADDRESS + ", " +
				COLUMN_ACCOUNT_HOME_STATE + ", " +
				COLUMN_ACCOUNT_HOME_CITY + ", " +
				COLUMN_ACCOUNT_HOME_ZIP +
				
				") VALUES (" +
				
				"?, ?, ?, ?, ?," +
				"?, ?, ?, ?, ?, " +
				"?, ?, ?, ?" +

				")";
		
		
		SQLiteStatement statement = db.compileStatement(insertStmt);
		
		statement.bindString(1, account.name);
		statement.bindDouble(2, account.id);
		statement.bindString(3, account.checkNum);
		statement.bindString(4, account.routeNum);
		statement.bindDouble(5, account.amount);
		
		statement.bindString(6, account.fullName);
		statement.bindString(7, account.birthdate);
		statement.bindString(8, account.socialSecurity);
		statement.bindString(9, account.employer);
		statement.bindString(10, account.phoneNumber);
		
		statement.bindString(11, account.homeAddress);
		statement.bindString(12, account.homeState);
		statement.bindString(13, account.homeCity);
		statement.bindDouble(14, Integer.parseInt(account.homeZip));
		
		statement.executeInsert();
		
		this.db.close();
		
		
	}
	
	
	/**
	 *  add an account to the Account Table
	 * @param name the name of the account
	 * @param checkNum the checking number of the account
	 * @param routeNum the routing number of the account
	 * @param amount the amount that is in the account (for test purposes)
	 */
	public void addAccount(String name, String checkNum, String routeNum, double amount){
		
		this.db = getWritableDatabase();
		
		String insertStatement = "INSERT INTO " + ACCOUNT_TABLE_NAME + " (" + COLUMN_ACCOUNT_NAME+"," + COLUMN_ACCOUNT_CHECKNUM + "," + COLUMN_ACCOUNT_ROUTENUM + "," + COLUMN_ACCOUNT_AMOUNT +
				"VALUES (?, ?, ?, ?)";
		
		SQLiteStatement statement = db.compileStatement(insertStatement);
		
		statement.bindString(1, name);
		statement.bindString(2, checkNum);
		statement.bindString(3, routeNum);
		statement.bindDouble(4, amount);
		
		statement.executeInsert();
		
		
	}
	
	/**
	 *  add an account to the Account Table. does not use getWritableDatabase. (used for onCreate method)
	 * @param name the name of the account
	 * @param checkNum the checking number of the account
	 * @param routeNum the routing number of the account
	 * @param amount the amount that is in the account (for test purposes)
	 */
	public void addAccountInit(SQLiteDatabase db, String name, String checkNum, String routeNum, double amount){
		
		
		String insertStatement = "INSERT INTO " + ACCOUNT_TABLE_NAME + " (" + COLUMN_ACCOUNT_NAME+"," + COLUMN_ACCOUNT_CHECKNUM + "," + COLUMN_ACCOUNT_ROUTENUM + "," + COLUMN_ACCOUNT_AMOUNT +
				") VALUES (?, ?, ?, ?)";
		
		SQLiteStatement statement = db.compileStatement(insertStatement);
		
		statement.bindString(1, name);
		statement.bindString(2, checkNum);
		statement.bindString(3, routeNum);
		statement.bindDouble(4, amount);
		
		statement.executeInsert();
		
		
	}
	
	/**
	 * adds an account with the given content values
	 * @param values the values containing all of the account information
	 */
	public void addAccount(ContentValues values){
		
		this.db = getWritableDatabase();
		
		db.insert(ACCOUNT_TABLE_NAME, null, values);
		
		
	}

	public boolean accountTableEmpty() {
		String query = "SELECT COUNT(*) FROM "+ ACCOUNT_TABLE_NAME + ";";
		
		SQLiteDatabase db = this.getReadableDatabase();
		
		Cursor results = db.rawQuery(query, null);
		
		if(results.moveToFirst()){
			
			int count = results.getInt(0);
			
			results.close();
			db.close();
			
			if(count < 1) return true;
			else return false;
			
			
		}

		results.close();
		db.close();
		
		return true;
		
	}
	
	
	public boolean hasDepositingAccount() {
		String query = "SELECT COUNT(*) FROM "+ ACCOUNT_TABLE_NAME + " WHERE " + COLUMN_ACCOUNT_NAME + "!= '" + DPS_ACCOUNT_NAME+"';";
		
		SQLiteDatabase db = this.getReadableDatabase();
		
		Cursor results = db.rawQuery(query, null);
		
		if(results.moveToFirst()){
			
			int count = results.getInt(0);
			
			if(count < 1) return true;
			else return false;
			
			
		}

		return true;
		
	}
	
	
	
//	/**
//	 *  returns a list of all the accounts in the local database
//	 * @return list of all accounts
//	 */
//	public ArrayList<Account> getAccountsAsObjects(){
//		
//		ArrayList<Account> accountList = new ArrayList<Account>();
//		
//		String query = "SELECT * FROM ?;";
//		
//		SQLiteDatabase db = getReadableDatabase();
//		
//		Cursor accountsCursor = db.rawQuery(query, new String[] {ACCOUNT_TABLE_NAME});
//		
//		while(accountsCursor.moveToNext()){
//			String name = accountsCursor.getString(accountsCursor.getColumnIndex(COLUMN_ACCOUNT_NAME));
//			String checkNum = accountsCursor.getString(accountsCursor.getColumnIndex(COLUMN_ACCOUNT_CHECKNUM));
//			String routeNum = accountsCursor.getString(accountsCursor.getColumnIndex(COLUMN_ACCOUNT_ROUTENUM));
//			double amount = accountsCursor.getDouble(accountsCursor.getColumnIndex(COLUMN_ACCOUNT_AMOUNT));
//			
//			accountList.add(new Account(name, checkNum, routeNum, amount));
//			
//			
//			
//		}
//		
//		return accountList;
//		
//	}
	
	/**
	 * Get all the accounts added in the account Database
	 * @return a cursor containing all the accounts information
	 */
public Cursor getAccountsAsCursor(){
		
		
		String query = "SELECT * FROM " + ACCOUNT_TABLE_NAME + ";";
		
		SQLiteDatabase db = getReadableDatabase();
		
		Cursor accountsCursor = db.rawQuery(query, null);
		
		return accountsCursor;
		
	}

	public Cursor getAccountsAsCursorNoDPS(){
		
		
		String query = "SELECT * FROM " + ACCOUNT_TABLE_NAME + " WHERE "+ COLUMN_ACCOUNT_NAME + " != '"+ DPS_ACCOUNT_NAME + "';";
		
		SQLiteDatabase db = getReadableDatabase();
		
		Cursor accountsCursor = db.rawQuery(query, null);
		
		return accountsCursor;
		
	}
	
	public Cursor getAccountAsCursor(String name){
		
		
		String query = "SELECT * FROM " + ACCOUNT_TABLE_NAME + " WHERE "+COLUMN_ACCOUNT_NAME+" = '" + name + "';";
		
		SQLiteDatabase db = getReadableDatabase();
		
		Cursor accountsCursor = db.rawQuery(query, null);
		
		return accountsCursor;
		
	}
	
	
	public Cursor getDPSAccountAsCursor(){
		
		String query = "SELECT * FROM " + ACCOUNT_TABLE_NAME + " WHERE " + COLUMN_ACCOUNT_NAME + " = " + DPS_ACCOUNT_NAME + ";";
		
		SQLiteDatabase db = getReadableDatabase();
		
		Cursor downPaymentAccountCursor = db.rawQuery(query, null);
		
		return downPaymentAccountCursor;
		
	}
	
	public Account getDPSAccountAsAccount(){
		
		String query = "SELECT * FROM " + ACCOUNT_TABLE_NAME + " WHERE " + COLUMN_ACCOUNT_NAME + " = '" + DPS_ACCOUNT_NAME + "';";
		
		SQLiteDatabase db = getReadableDatabase();
		
		Cursor downPaymentAccountCursor = db.rawQuery(query, null);
		
		Account dps =  new Account(downPaymentAccountCursor);
		
		downPaymentAccountCursor.close();
		db.close();
		
		return dps;
		
	}
	
	public Account getAccountAsAccount(String name){
		
		String query = "SELECT * FROM " + ACCOUNT_TABLE_NAME + " WHERE " + COLUMN_ACCOUNT_NAME + " = '" + name + "';";
		
		this.db = getReadableDatabase();
		
		Cursor cursor = db.rawQuery(query, null);
		
		Account account = new Account(cursor);
		
		cursor.close();
		db.close();
		
		return account;
		
	}
	
	
	/**
	 * gets the ID of the given account name
	 * @param name tthe name of the account (String)
	 * @return
	 */
	public int getAccountID(String name){
		
		String query = "SELECT " + COLUMN_ACCOUNT_ID + " FROM " + ACCOUNT_TABLE_NAME + " WHERE " + COLUMN_ACCOUNT_NAME + " = '" + name + "';";
		 
		
		SQLiteDatabase db = getReadableDatabase();
		
		Cursor result = db.rawQuery(query, null);
		
		result.moveToFirst();
		
		if(result.getCount() >= 1){
			int id = result.getInt(result.getColumnIndex(COLUMN_ACCOUNT_ID));
			
			Log.e("ACCOUNT ID" , " ID = " + id  + "FOR ACCOUNT + "  + name);
			
			return id;
		}
		else{
			
			return -1;
			
		}

		
		
		
	}
	
	/**
	 * transfer money from one account to another. 
	 * @param from the account to take money from
	 * @param to the account to put money in
	 * @param amount the amount to remove/add
	 */
	public boolean transferMoney(String from, String to, int amount){
		
		/** remove money from "from" account **/
		Account fromAccount = getAccountAsAccount(from);
		this.db.close();
		Account toAccount = getAccountAsAccount(to);
		this.db.close();
		
		
		Log.e(tag, " fromAccount = " + fromAccount.amount +  " amount to transfer = " + amount);
		
			/** does the account have enough money? **/
			/** TODO add error checking **/
			if(fromAccount.amount < amount) return false;
			else{
				/** add/subtract amount **/
				fromAccount.amount -= amount;
				toAccount.amount += amount;
				
				/** update accounts in DB **/
				updateAccount(fromAccount);
				updateAccount(toAccount);
				
				return true;
				
			}
		
		
		
		
		
	}
	
	
	/**
	 * updates an account in the DB with the information given in the Account Object 
	 * @param account object containing account information
	 */
	public void updateAccount(Account account){
		
		ContentValues values = new ContentValues();
		values.put(COLUMN_ACCOUNT_NAME, account.name);
		values.put(COLUMN_ACCOUNT_CHECKNUM, account.checkNum);
		values.put(COLUMN_ACCOUNT_ROUTENUM, account.routeNum);
		values.put(COLUMN_ACCOUNT_AMOUNT, account.amount);
		
		values.put(COLUMN_ACCOUNT_FULLNAME, account.fullName);
		values.put(COLUMN_ACCOUNT_BIRTHDATE, account.birthdate);
		values.put(COLUMN_ACCOUNT_PHONE_NUMBER, account.phoneNumber);
		values.put(COLUMN_ACCOUNT_HOME_ADDRESS, account.homeAddress);
		values.put(COLUMN_ACCOUNT_HOME_CITY, account.homeCity);
		values.put(COLUMN_ACCOUNT_HOME_STATE, account.homeState);
		values.put(COLUMN_ACCOUNT_HOME_ZIP, account.homeZip);
		
		SQLiteDatabase db = getWritableDatabase();
		db.update(ACCOUNT_TABLE_NAME, values, COLUMN_ACCOUNT_ID+"=?", new String[]{ String.valueOf(account.id)});
		
		
	}
	
	public void deleteAccounts(){
		
		this.db=getWritableDatabase();
		
		this.db.execSQL("DELETE FROM Accounts");
		
		this.db.close();
	}

	public void editAccount(Account account) {
		
		this.db= getWritableDatabase();
		
		ContentValues values = new ContentValues();
		
		values.put(COLUMN_ACCOUNT_NAME, account.name);
		
		values.put(COLUMN_ACCOUNT_FULLNAME, account.fullName);
		values.put(COLUMN_ACCOUNT_BIRTHDATE, account.birthdate);
		values.put(COLUMN_ACCOUNT_SOCIAL_SECURITY, account.socialSecurity);
		values.put(COLUMN_ACCOUNT_EMPLOYER, account.employer);
		values.put(COLUMN_ACCOUNT_PHONE_NUMBER, account.phoneNumber);
		values.put(COLUMN_ACCOUNT_HOME_ADDRESS, account.homeAddress);
		values.put(COLUMN_ACCOUNT_HOME_CITY, account.homeCity);
		values.put(COLUMN_ACCOUNT_HOME_STATE, account.homeState);
		values.put(COLUMN_ACCOUNT_HOME_ZIP, account.homeZip);
		
		values.put(COLUMN_ACCOUNT_CHECKNUM, account.checkNum);
		values.put(COLUMN_ACCOUNT_ROUTENUM, account.routeNum);
		
		
		Log.e("editAccount", account.fullName);
		Log.e("editAccount DBHElp" , " acoutn id = " + account.id);
		
		int result = this.db.update(ACCOUNT_TABLE_NAME, values, COLUMN_ACCOUNT_ID + " = ?", new String[] { String.valueOf(account.id) });
		
		Log.e("editAccountDBHELP", "rows affected = " + result);
		
		
		
	}
	
	public void deleteAccount(String accountName) {
		
		this.db = getWritableDatabase();
		
		int selectedAccountID = this.getAccountID(accountName);

		this.db.execSQL("DELETE FROM " + ACCOUNT_TABLE_NAME + " WHERE " + COLUMN_ACCOUNT_ID + " = " + selectedAccountID);
		
		this.db.close();
		
	}




	public void deleteAccount(int selectedAccountID) {
		
		this.db = getWritableDatabase();
		
		this.db.execSQL("DELETE FROM " + ACCOUNT_TABLE_NAME + " WHERE " + COLUMN_ACCOUNT_ID + " = " + selectedAccountID);
		
		this.db.close();
		
	}

	public boolean hasDPSAccount() {
		String query = "SELECT COUNT(*) FROM "+ ACCOUNT_TABLE_NAME + " WHERE " + COLUMN_ACCOUNT_NAME + "= '" + DPS_ACCOUNT_NAME+"';";
		
		SQLiteDatabase db = this.getReadableDatabase();
		
		Cursor results = db.rawQuery(query, null);
		
		if(results.moveToFirst()){
			
			int count = results.getInt(0);
			
			if(count < 1) return false;
			else return true;
			
			
		}

		return false;
		
	}

	public void deleteAll() {
		this.db = getWritableDatabase();
		
		this.db.execSQL("DROP TABLE IF EXISTS " + ACCOUNT_TABLE_NAME);
		
		this.db.execSQL(CREATE_ACCOUNTS_TABLE);
		
		this.db.close();
		
	}
	
	
	

}
